﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Data.OleDb;
using System.Data;
using System.Xml.Serialization;
using System.IO;
using System.Diagnostics;

namespace hedefgrup.reports.renault.Services
{
    [ComVisible(true)]
    public class OptInOutUpdate
    {
        private const string webServicePath = "https://partner.mais.com.tr:8181/RenaultGCTServices/RenaultGCTServiceService";

        private static bool RemoteCertificateValidationCallback(object sender, System.Security.Cryptography.X509Certificates.X509Certificate certificate, System.Security.Cryptography.X509Certificates.X509Chain chain, System.Net.Security.SslPolicyErrors sslPolicyErrors)
        {
            return true;
        }

        public string Execute(string yearAndMonth, string accessDatabasePath, int intervieweeID, string saveXmlRequestPath)
        {
            System.Net.ServicePointManager.ServerCertificateValidationCallback = RemoteCertificateValidationCallback;

            List<RenaultBusinessServices.renaultCrmOptBilgi> recordsToUpdate = new List<RenaultBusinessServices.renaultCrmOptBilgi>();

            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + accessDatabasePath.Replace("\\", "\\\\") + ";Persist Security Info=False;";
            OleDbConnection databaseConnection = new OleDbConnection(connectionString);
            databaseConnection.Open();

            OleDbCommand command = new OleDbCommand("SELECT * FROM tblExtraData WHERE intervieweeID = " + (intervieweeID > 0 ? intervieweeID.ToString() : "intervieweeID") + " AND (OPTINSERVED IS NULL OR OPTINSERVED <> '1') AND OPTINDATE IS NOT NULL AND OPTINDATE <> ''", databaseConnection);
            OleDbDataAdapter adapter = new OleDbDataAdapter(command);
            DataTable recordsDataTable = new DataTable();
            adapter.Fill(recordsDataTable);

            adapter.Dispose();
            command.Connection.Close();
            command.Connection.Dispose();
            command.Dispose();

            if (recordsDataTable.Rows.Count == 0)
            {
                return "NO RECORDS TO UPDATE";
            }

            string nowString = DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");

            foreach (DataRow recordToUpdate in recordsDataTable.Rows)
            {
                bool optInSMS = recordToUpdate["OPTIN_SMS"].ToString() == "1";
                bool optInEMail = recordToUpdate["OPTIN_EMAIL"].ToString() == "1";
                bool optInPhone = recordToUpdate["OPTIN_TEL"].ToString() == "1";
                //bool optInMail = recordToUpdate["OPTIN_POSTA"].ToString() == "1";
                //bool optInFax = recordToUpdate["OPTIN_FAX"].ToString() == "1";
                string nameLastName = recordToUpdate["MUSTERI_ADI"].ToString();
                string name = nameLastName;
                string lastName = nameLastName;
                int lastIndexOfWS = nameLastName.LastIndexOf(' ');
                if (lastIndexOfWS > 0 && lastIndexOfWS < nameLastName.Length - 1)
                {
                    name = nameLastName.Substring(0, lastIndexOfWS);
                    lastName = nameLastName.Substring(lastIndexOfWS + 1);
                }

                recordsToUpdate.Add
                (
                    new RenaultBusinessServices.renaultCrmOptBilgi()
                    {
                        adi = name,
                        cep = string.IsNullOrEmpty(recordToUpdate["CEP_TEL"].ToString()) ? null : recordToUpdate["CEP_TEL"].ToString(),
                        e_mail = string.IsNullOrEmpty(recordToUpdate["EMAIL"].ToString()) || recordToUpdate["EMAIL"].ToString().ToUpperInvariant() == "YOK" ? null : recordToUpdate["EMAIL"].ToString(),
                        e_mail_aco = optInEMail ? "Y" : "N",
                        e_mail_aco_date = nowString,
                        genel_aco = optInSMS && optInEMail && optInPhone ? "Y" : "N",
                        genel_aco_date = nowString,
                        il = recordToUpdate["SEHIR"].ToString(),
                        ilce = recordToUpdate["ILCE"].ToString(),
                        kanit_id = yearAndMonth + "_" + recordToUpdate["BAYII_KODU"].ToString().Replace("_D", "").Replace("_R", "") + "-" + recordToUpdate["intervieweeID"].ToString() + ".wav",
                        kanit_tarihi = nowString,
                        marka = recordToUpdate["MARKA"].ToString().ToUpperInvariant(),
                        sasi = recordToUpdate["SASI_NO"].ToString(),
                        sms_aco = optInSMS ? "Y" : "N",
                        sms_aco_date = nowString,
                        soyadi = lastName,
                        tc = recordToUpdate["TC_NO"].ToString(),
                        tel_aco = optInPhone ? "Y" : "N",
                        tel_aco_date = nowString,
                    }
                );
            }

            RenaultBusinessServices.RenaultGCTServiceService serviceInstance = new RenaultBusinessServices.RenaultGCTServiceService();
            serviceInstance.Url = webServicePath;
            serviceInstance.Timeout = 1000 * 60 * 15;

            string result = null;
            try
            {
                result = serviceInstance.putCrmOpt("RENAULT_DINLIYOR", recordsToUpdate.ToArray());
                if (!string.IsNullOrEmpty(saveXmlRequestPath))
                {
                    MemoryStream memStream = new MemoryStream();
                    new XmlSerializer(typeof(RenaultBusinessServices.renaultCrmOptBilgi[])).Serialize(memStream, recordsToUpdate.ToArray());
                    memStream.Position = 0;
                    StreamReader reader = new StreamReader(memStream);
                    File.WriteAllText(saveXmlRequestPath, reader.ReadToEnd());
                    reader.Dispose();
                    reader.Close();
                }
            }
            catch (Exception serviceCallException)
            {
                result = serviceCallException.ToString();
            }

            return "Passed " + recordsToUpdate.Count + " records. Result: " + result;
        }

    }
}
